﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SGAEntidades;
using SGABDUtil;
using SGAInterfaces;

namespace SGAAccesoDatos
{
    public class Vehiculo : IVehiculo
    {
        #region Miembros de IVehiculo

        public IList<VehiculoInfo> Listar(VehiculoInfo oVehiculo)
        {
            var sqlParm = new SqlParameter[6];
            var oListaVehiculos = new List<VehiculoInfo>();

            sqlParm[0] = new SqlParameter("@Placa", SqlDbType.VarChar);
            if (oVehiculo.Placa != null) { sqlParm[0].Value = oVehiculo.Placa; } else { sqlParm[0].Value = DBNull.Value; }

            sqlParm[1] = new SqlParameter("@Id_Marca_Vehiculo", SqlDbType.VarChar);
            if (oVehiculo.IdMarcaVehiculo != null) { sqlParm[1].Value = oVehiculo.IdMarcaVehiculo; } else { sqlParm[1].Value = DBNull.Value; }

            sqlParm[2] = new SqlParameter("@Id_Modelo_Vehiculo", SqlDbType.VarChar);
            if (oVehiculo.IdModeloVehiculo != null) { sqlParm[2].Value = oVehiculo.IdModeloVehiculo; } else { sqlParm[2].Value = DBNull.Value; }

            sqlParm[3] = new SqlParameter("@Id_Tipo_Propiedad", SqlDbType.VarChar);
            if (oVehiculo.IdTipoPropiedad != null) { sqlParm[3].Value = oVehiculo.IdTipoPropiedad; } else { sqlParm[3].Value = DBNull.Value; }

            sqlParm[4] = new SqlParameter("@Tarjeta_Propiedad", SqlDbType.VarChar);
            if (oVehiculo.TarjetaPropiedad != null) { sqlParm[4].Value = oVehiculo.TarjetaPropiedad; } else { sqlParm[4].Value = DBNull.Value; }

            sqlParm[5] = new SqlParameter("@Poliza", SqlDbType.VarChar);
            if (oVehiculo.Poliza != null) { sqlParm[5].Value = oVehiculo.Poliza; } else { sqlParm[5].Value = DBNull.Value; }
            
            using (
                var drd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringDistributedTransaction,
                                                  CommandType.StoredProcedure, "SGA_SP_CONSULTAR_VEHICULO", sqlParm))
            {
                if (drd != null)
                {
                    if (drd.HasRows)
                    {
                        while (drd.Read())
                        {
                            oListaVehiculos.Add(new VehiculoInfo(drd.GetString(0).Trim(), drd.GetString(1).Trim(),
                                                                 drd.GetString(2).Trim(),
                                                                 drd.GetString(3).Trim(),
                                                                 new MarcaVehiculoInfo(drd.GetString(3).Trim(),
                                                                                       drd.GetString(4).Trim()),
                                                                 drd.GetString(5).Trim(),
                                                                 new ModeloVehiculoInfo(drd.GetString(5).Trim(), null,
                                                                                        drd.GetString(6).Trim()),
                                                                 drd.GetString(7).Trim(),
                                                                 new TipoCarroceriaInfo(drd.GetString(7).Trim(),
                                                                                        drd.GetString(8).Trim()),
                                                                 drd.GetString(9).Trim(),
                                                                 new TipoPropiedadInfo(drd.GetString(9).Trim(),
                                                                                       drd.GetString(10).Trim()),
                                                                 Convert.ToInt32(drd.GetValue(11).ToString()),
                                                                 drd.GetString(12).Trim(),
                                                                 Double.Parse(drd.GetValue(13).ToString()), null, 
                                                                 drd.GetString(14).Trim(), new TipoCombustibleInfo(drd.GetString(14).Trim(), drd.GetString(15).Trim()),
                                                                 drd.GetString(16).Trim(), drd.GetString(17).Trim(), drd.GetDateTime(18), Int32.Parse(drd.GetValue(19).ToString())
                                                    ));
                        }
                    }
                }
                return oListaVehiculos;
            }
        }

        public VehiculoInfo Consultar(VehiculoInfo oVehiculo)
        {
            var sqlParm = new SqlParameter[6];
            var oEntVehiculo = new VehiculoInfo();

            sqlParm[0] = new SqlParameter("@Placa", SqlDbType.VarChar);
            if (oVehiculo.Placa != null) { sqlParm[0].Value = oVehiculo.Placa; } else { sqlParm[0].Value = DBNull.Value; }

            sqlParm[1] = new SqlParameter("@Id_Marca_Vehiculo", SqlDbType.VarChar);
            if (oVehiculo.IdMarcaVehiculo != null) { sqlParm[1].Value = oVehiculo.IdMarcaVehiculo; } else { sqlParm[1].Value = DBNull.Value; }

            sqlParm[2] = new SqlParameter("@Id_Modelo_Vehiculo", SqlDbType.VarChar);
            if (oVehiculo.IdModeloVehiculo != null) { sqlParm[2].Value = oVehiculo.IdModeloVehiculo; } else { sqlParm[2].Value = DBNull.Value; }

            sqlParm[3] = new SqlParameter("@Id_Tipo_Propiedad", SqlDbType.VarChar);
            if (oVehiculo.IdTipoPropiedad != null) { sqlParm[3].Value = oVehiculo.IdTipoPropiedad; } else { sqlParm[3].Value = DBNull.Value; }

            sqlParm[4] = new SqlParameter("@Tarjeta_Propiedad", SqlDbType.VarChar);
            if (oVehiculo.TarjetaPropiedad != null) { sqlParm[4].Value = oVehiculo.TarjetaPropiedad; } else { sqlParm[4].Value = DBNull.Value; }

            sqlParm[5] = new SqlParameter("@Poliza", SqlDbType.VarChar);
            if (oVehiculo.Poliza != null) { sqlParm[5].Value = oVehiculo.Poliza; } else { sqlParm[5].Value = DBNull.Value; }

            using (
                var drd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringDistributedTransaction,
                                                  CommandType.StoredProcedure, "SGA_SP_CONSULTAR_VEHICULO", sqlParm))
            {
                if (drd != null)
                {
                    if (drd.HasRows)
                    {
                        drd.Read();
                        oEntVehiculo = new VehiculoInfo(drd.GetString(0).Trim(), drd.GetString(1).Trim(),
                                                        drd.GetString(2).Trim(),
                                                        drd.GetString(3).Trim(),
                                                        new MarcaVehiculoInfo(drd.GetString(3).Trim(),
                                                                                drd.GetString(4).Trim()),
                                                        drd.GetString(5).Trim(),
                                                        new ModeloVehiculoInfo(drd.GetString(5).Trim(), null,
                                                                                drd.GetString(6).Trim()),
                                                        drd.GetString(7).Trim(),
                                                        new TipoCarroceriaInfo(drd.GetString(7).Trim(),
                                                                                drd.GetString(8).Trim()),
                                                        drd.GetString(9).Trim(),
                                                        new TipoPropiedadInfo(drd.GetString(9).Trim(),
                                                                                drd.GetString(10).Trim()),
                                                        Convert.ToInt32(drd.GetValue(11).ToString()),
                                                        drd.GetString(12).Trim(),
                                                        Double.Parse(drd.GetValue(13).ToString()), null,
                                                        drd.GetString(14).Trim(), new TipoCombustibleInfo(drd.GetString(14).Trim(), drd.GetString(15).Trim()),
                                                        drd.GetString(16).Trim(), drd.GetString(17).Trim(), drd.GetDateTime(18), Int32.Parse(drd.GetValue(19).ToString())
                            );
                        
                    }
                }
                return oEntVehiculo;
            }
        }

        public bool Registrar(VehiculoInfo oVehiculo)
        {
            var sqlParm = new SqlParameter[16];
            using (var con = new SqlConnection(SqlHelper.ConnectionStringDistributedTransaction))
            {
                con.Open();
                var trx = con.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    sqlParm[0] = new SqlParameter("@Placa", SqlDbType.VarChar);
                    if (oVehiculo.Placa != null) { sqlParm[0].Value = oVehiculo.Placa; } else { sqlParm[0].Value = DBNull.Value; }

                    sqlParm[1] = new SqlParameter("@Motor", SqlDbType.VarChar);
                    if (oVehiculo.Motor != null) { sqlParm[1].Value = oVehiculo.Motor; } else { sqlParm[1].Value = DBNull.Value; }

                    sqlParm[2] = new SqlParameter("@Chasis", SqlDbType.VarChar);
                    if (oVehiculo.Chasis != null) { sqlParm[2].Value = oVehiculo.Chasis; } else { sqlParm[2].Value = DBNull.Value; }

                    sqlParm[3] = new SqlParameter("@Id_Marca_Vehiculo", SqlDbType.VarChar);
                    if (oVehiculo.IdMarcaVehiculo != null) { sqlParm[3].Value = oVehiculo.IdMarcaVehiculo; } else { sqlParm[3].Value = DBNull.Value; }

                    sqlParm[4] = new SqlParameter("@Id_Modelo_Vehiculo", SqlDbType.VarChar);
                    if (oVehiculo.IdModeloVehiculo != null) { sqlParm[4].Value = oVehiculo.IdModeloVehiculo; } else { sqlParm[4].Value = DBNull.Value; }

                    sqlParm[5] = new SqlParameter("@Id_Tipo_Carroceria_Vehiculo", SqlDbType.VarChar);
                    if (oVehiculo.IdTipoCarroceria != null) { sqlParm[5].Value = oVehiculo.IdTipoCarroceria; } else { sqlParm[5].Value = DBNull.Value; }

                    sqlParm[6] = new SqlParameter("@Id_Tipo_Propiedad", SqlDbType.VarChar);
                    if (oVehiculo.IdTipoPropiedad != null) { sqlParm[6].Value = oVehiculo.IdTipoPropiedad; } else { sqlParm[6].Value = DBNull.Value; }

                    sqlParm[7] = new SqlParameter("@Año_Fabricacion", SqlDbType.Int);
                    if (oVehiculo.AñoFabricacion.HasValue) { sqlParm[7].Value = oVehiculo.AñoFabricacion; } else { sqlParm[7].Value = DBNull.Value; }

                    sqlParm[8] = new SqlParameter("@Color", SqlDbType.VarChar);
                    if (oVehiculo.Color != null) { sqlParm[8].Value = oVehiculo.Color; } else { sqlParm[8].Value = DBNull.Value; }

                    sqlParm[9] = new SqlParameter("@Galon_X_Km", SqlDbType.Decimal);
                    if (oVehiculo.GalonesXKm.HasValue) { sqlParm[9].Value = oVehiculo.GalonesXKm; } else { sqlParm[9].Value = DBNull.Value; }

                    sqlParm[10] = new SqlParameter("@Usuario_Sistema", SqlDbType.VarChar);
                    if (oVehiculo.UsuarioSistema != null) { sqlParm[10].Value = oVehiculo.UsuarioSistema; } else { sqlParm[10].Value = DBNull.Value; }

                    sqlParm[11] = new SqlParameter("@Id_Tipo_Combustible", SqlDbType.VarChar);
                    if (oVehiculo.IdTipoCombustible != null) { sqlParm[11].Value = oVehiculo.IdTipoCombustible; } else { sqlParm[11].Value = DBNull.Value; }

                    sqlParm[12] = new SqlParameter("@Tarjeta_Propiedad", SqlDbType.VarChar);
                    if (oVehiculo.TarjetaPropiedad != null) { sqlParm[12].Value = oVehiculo.TarjetaPropiedad; } else { sqlParm[12].Value = DBNull.Value; }

                    sqlParm[13] = new SqlParameter("@Poliza", SqlDbType.VarChar);
                    if (oVehiculo.Poliza != null) { sqlParm[13].Value = oVehiculo.Poliza; } else { sqlParm[13].Value = DBNull.Value; }

                    sqlParm[14] = new SqlParameter("@Fecha_Soat", SqlDbType.DateTime);
                    if (oVehiculo.FechaSoat.HasValue) { sqlParm[14].Value = oVehiculo.FechaSoat; } else { sqlParm[14].Value = DBNull.Value; }

                    sqlParm[15] = new SqlParameter("@Nro_Ocupantes", SqlDbType.Int);
                    if (oVehiculo.NroOcupantes.HasValue) { sqlParm[15].Value = oVehiculo.NroOcupantes; } else { sqlParm[15].Value = DBNull.Value; }

                    SqlHelper.ExecuteNonQuery(trx, CommandType.StoredProcedure, "SGA_SP_REGISTRAR_VEHICULO", sqlParm);
                    trx.Commit();
                }
                catch (Exception)
                {
                    trx.Rollback();
                    return false;
                }
                finally
                {
                    con.Close();
                }
            }
            return true;
        }

        public bool Actualizar(VehiculoInfo oVehiculo)
        {
            var sqlParm = new SqlParameter[16];
            using (var con = new SqlConnection(SqlHelper.ConnectionStringDistributedTransaction))
            {
                con.Open();
                var trx = con.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    sqlParm[0] = new SqlParameter("@Placa", SqlDbType.VarChar);
                    if (oVehiculo.Placa != null) { sqlParm[0].Value = oVehiculo.Placa; } else { sqlParm[0].Value = DBNull.Value; }

                    sqlParm[1] = new SqlParameter("@Motor", SqlDbType.VarChar);
                    if (oVehiculo.Motor != null) { sqlParm[1].Value = oVehiculo.Motor; } else { sqlParm[1].Value = DBNull.Value; }

                    sqlParm[2] = new SqlParameter("@Chasis", SqlDbType.VarChar);
                    if (oVehiculo.Chasis != null) { sqlParm[2].Value = oVehiculo.Chasis; } else { sqlParm[2].Value = DBNull.Value; }

                    sqlParm[3] = new SqlParameter("@Id_Marca_Vehiculo", SqlDbType.VarChar);
                    if (oVehiculo.IdMarcaVehiculo != null) { sqlParm[3].Value = oVehiculo.IdMarcaVehiculo; } else { sqlParm[3].Value = DBNull.Value; }

                    sqlParm[4] = new SqlParameter("@Id_Modelo_Vehiculo", SqlDbType.VarChar);
                    if (oVehiculo.IdModeloVehiculo != null) { sqlParm[4].Value = oVehiculo.IdModeloVehiculo; } else { sqlParm[4].Value = DBNull.Value; }

                    sqlParm[5] = new SqlParameter("@Id_Tipo_Carroceria_Vehiculo", SqlDbType.VarChar);
                    if (oVehiculo.IdTipoCarroceria != null) { sqlParm[5].Value = oVehiculo.IdTipoCarroceria; } else { sqlParm[5].Value = DBNull.Value; }

                    sqlParm[6] = new SqlParameter("@Id_Tipo_Propiedad", SqlDbType.VarChar);
                    if (oVehiculo.IdTipoPropiedad != null) { sqlParm[6].Value = oVehiculo.IdTipoPropiedad; } else { sqlParm[6].Value = DBNull.Value; }

                    sqlParm[7] = new SqlParameter("@Año_Fabricacion", SqlDbType.Int);
                    if (oVehiculo.AñoFabricacion.HasValue) { sqlParm[7].Value = oVehiculo.AñoFabricacion; } else { sqlParm[7].Value = DBNull.Value; }

                    sqlParm[8] = new SqlParameter("@Color", SqlDbType.VarChar);
                    if (oVehiculo.Color != null) { sqlParm[8].Value = oVehiculo.Color; } else { sqlParm[8].Value = DBNull.Value; }

                    sqlParm[9] = new SqlParameter("@Galon_X_Km", SqlDbType.Decimal);
                    if (oVehiculo.GalonesXKm.HasValue) { sqlParm[9].Value = oVehiculo.GalonesXKm; } else { sqlParm[9].Value = DBNull.Value; }

                    sqlParm[10] = new SqlParameter("@Usuario_Sistema", SqlDbType.VarChar);
                    if (oVehiculo.UsuarioSistema != null) { sqlParm[10].Value = oVehiculo.UsuarioSistema; } else { sqlParm[10].Value = DBNull.Value; }

                    sqlParm[11] = new SqlParameter("@Id_Tipo_Combustible", SqlDbType.VarChar);
                    if (oVehiculo.IdTipoCombustible != null) { sqlParm[11].Value = oVehiculo.IdTipoCombustible; } else { sqlParm[11].Value = DBNull.Value; }

                    sqlParm[12] = new SqlParameter("@Tarjeta_Propiedad", SqlDbType.VarChar);
                    if (oVehiculo.TarjetaPropiedad != null) { sqlParm[12].Value = oVehiculo.TarjetaPropiedad; } else { sqlParm[12].Value = DBNull.Value; }

                    sqlParm[13] = new SqlParameter("@Poliza", SqlDbType.VarChar);
                    if (oVehiculo.Poliza != null) { sqlParm[13].Value = oVehiculo.Poliza; } else { sqlParm[13].Value = DBNull.Value; }

                    sqlParm[14] = new SqlParameter("@Fecha_Soat", SqlDbType.DateTime);
                    if (oVehiculo.FechaSoat.HasValue) { sqlParm[14].Value = oVehiculo.FechaSoat; } else { sqlParm[14].Value = DBNull.Value; }

                    sqlParm[15] = new SqlParameter("@Nro_Ocupantes", SqlDbType.Int);
                    if (oVehiculo.NroOcupantes.HasValue) { sqlParm[15].Value = oVehiculo.NroOcupantes; } else { sqlParm[15].Value = DBNull.Value; }

                    SqlHelper.ExecuteNonQuery(trx, CommandType.StoredProcedure, "SGA_SP_ACTUALIZAR_VEHICULO", sqlParm);
                    trx.Commit();
                }
                catch (Exception)
                {
                    trx.Rollback();
                    return false;
                }
                finally
                {
                    con.Close();
                }
            }
            return true;
        }

        #endregion
    }
}
